<?php
namespace app\index\controller;
use think\Controller;
use think\Session;
use think\Request;
use think\View;
use think\Db;
use think\Model;
use PHPExcel;
use PHPExcel_IOFactory;
//AP预算模块
class Yusuandaoru extends Commonwork
{	
	
	public function yusuanbaocun(){
		$request = Request::instance(); $data=$request->post();
		$aid=$data["tid"];
        $tmp=Db::table("target_apply")->where(["id"=>$aid])->find();
        $state=$tmp["state2"];
        if($state==1){
        	$fid=8;
        	//$resa=$this->goworkera($fid,$aid,$data["nodeKey"],$data["selectId"],$data["selectName"]);
        	//return json_encode(["msg"=>"暂停","state"=>2]);
        }else{
        	$fid=9;
        	
        }
       // print_r($data);exit;
        $type_id=$tmp["type_id"];
        $type_name=Db::Table("target_format_type")->where(["id"=>$type_id])->value("format_type");
        $resa=$this->goworker($fid,$aid,$data["nodeKey"],$data["selectId"],$data["selectName"]);
        if($resa["operateStatus"]=="success"){
        	$target=Db::table("target_apply")->where(["id"=>$data['tid']])->find();
	        $biaoti=trim($data["title"]);
	        $remark=trim(htmlspecialchars($data["remark"]));
	        $state=trim($data["type"]);
	       // $shenpiren=trim($data["shenpiren"]);
	        $file = request()->file('wenjian');
	        $tid=$data['tid'];

	        if($data["attach"])
	            $fujian=$data["attach"];
	        else
	            $fujian=null;
	        $path=null;
	        if($file){
	            $info = $file->validate(['size'=>2*1024*1024,'ext'=>'xls'])->move(ROOT_PATH . 'public/uploads/YuSuan' . DS );
	            if($info){
	                // 成功上传后 获取上传信息
	               
	                // 输出
	                $savename= $info->getSaveName();
	                $path=ROOT_PATH . 'public/uploads/YuSuan/'.$savename;

	                $res=$this->excel_xiugai($type_name,$tid,$path);

	                $res=json_decode($res,true);
	                
	            }else{
	                // 上传失败获取错误信息
	                return json_encode(["msg"=>$file->getError(),"state"=>2]);
	               
	            }
	        }else{
	            $res=["state"=>1,"msg"=>""];
	        }
	        if($res["state"]==1){
	            try{
	                 if($path){
	                 	$res1=Db::table("target_apply")->where(["id"=>$tid])->update(["title"=>$biaoti,"remark"=>$remark,"fujian"=>$fujian,"state"=>3,"path"=>$path]);
	                }else{
	                     $res1=Db::table("target_apply")->where(["id"=>$tid])->update(["title"=>$biaoti,"remark"=>$remark,"fujian"=>$fujian,"state"=>3]);
	                }
	            }catch(\Exception $e){
	             	return json_encode(["msg"=>"系统繁忙请稍后再试","state"=>2]);
	            }
	            //$res=["state"=>1,"msg"=>""];
	           return json_encode($res);
	        }else{
	             return json_encode($res);
	        }
        }else{
        	return json_encode(["msg"=>"系统繁忙请稍后再试","state"=>2]);
        }
	}

	public function excel_xiugai($title,$tid,$filename){

			//$title="AP费用预算格式";
		Vendor("PHPExcel");
        Vendor("PHPExcel.IOFactory");
        
       	Vendor("PHPExcel.Reader.Excel5");
       	//$filename=ROOT_PATH . 'public/a.xls';
       	$objReader = PHPExcel_IOFactory::createReaderForFile($filename);
		$objPHPExcel = $objReader->load($filename);
		$sheet=$objPHPExcel->setActiveSheetIndex(0);
		$highestRow = $sheet->getHighestRow(); // 取得总行数
		$highestColumm = $sheet->getHighestColumn(); // 取得总列数
		
		//获取基本格式
		$target=Db::table("target_format")->alias("tf")->join("target_format_type tft","tf.type_id=tft.id")
        ->where(["tft.format_type"=>$title,"tf.is_actived"=>1,"tf.is_deleted"=>0])->find();

        $datatianxie=[];
        if($target["channel_id"]){
        	$datatianxie[]="channel_id";
        	$tmp=Db::table("field")->where(["id"=>$target["channel_id"]])->find();
        	$qudao=$tmp["field_name"];
        }
        if($target["client_id"]){
        	$datatianxie[]="client_id";
        	$tmp=Db::table("field")->where(["id"=>$target["client_id"]])->find();
        	$client=$tmp["field_name"];
        }
        if($target["product_id"]){
        	$datatianxie[]="product_id";
        	$tmp=Db::table("field")->where(["id"=>$target["product_id"]])->find();
        	$product=$tmp["field_name"];
        }
        if($target["area_id"]){
        	$datatianxie[]="area_id";
        	$tmp=Db::table("field")->where(["id"=>$target["area_id"]])->find();
        	$area=$tmp["field_name"];
        }
        if($target["feiyong_id"]){
        	$datatianxie[]="feiyong_id";
        	$tmp=Db::table("field")->where(["id"=>$target["feiyong_id"]])->find();
        	$feiyong=$tmp["field_name"];
        }

       
        	
       
        if($target["kaoheqi"]){
        	$datatianxie[]="cainian";
        	$datatianxie[]="kaoheqi";
        	$tmp=Db::table("field")->where(["id"=>$target["kaoheqi"]])->find();
        	$kaoheqi=$tmp["field_name"];
        }
        if($target["people_id"]){
        	$datatianxie[]="people_id";
        }
        if($target["sale_id"]){
        	$datatianxie[]="sale_id";
        }
        if($target["sale_real_id"]){
        	$datatianxie[]="sale_real_id";
        }
        if($target["profit_id"]){
        	$datatianxie[]="profit_id";
        }
        if($target["depart_id"]){
        	$datatianxie[]="depart_id";
        }
        if($title!="集团目标格式"){
        	 $datatianxie[]="yusuan";
        }
       
       // print_r($datatianxie);exit;
        //$
       $alldata=[];
      
       	
       	$conunt_de=0;
        for ($row = 3; $row <= $highestRow; $row++){//行数是以第1行开始
        	if(!trim($sheet->getCell('A'.$row)->getValue())){
        		break;
        	}
        	$conunt_de++;
        	$strcountde=sprintf("%03d",$conunt_de);
        	 $adddata=["detail_no"=>$strcountde];
			for ($column = 0; $column < count($datatianxie); $column++) {//列数是以A列开始
				$c=chr($column+65);//echo $c;exit;
				$content=trim($sheet->getCell($c.$row)->getValue());
				switch ($datatianxie[$column]) {
					case 'channel_id':
						# code...渠道编号
						if($content){
							if($qudao=="一级渠道"){
							$tmpqudao=Db::table("cg_channel_types1")->where(["title"=>$content])->field("id,title")->find();
								if($tmpqudao){
									$adddata["channel1"]=$tmpqudao["id"];
								}else{
									
									return json_encode(["msg"=>"一级渠道不存在或已删除","state"=>2]);
								}
							}else if($qudao=="二级渠道"){
								$tmpqudao=Db::table("cg_channel_types2")->alias("c2")
								->join("cg_channel_types1 c1","c2.pid=c1.id")
								->where(["c2.title"=>$content])
								->field("c1.id as title1,c2.id as title2")
								->find();
								if($tmpqudao){
									$adddata["channel1"]=$tmpqudao["title1"];
									$adddata["channel2"]=$tmpqudao["title2"];
								}else{
									
									return json_encode(["msg"=>"二级渠道不存在或上级渠道已删除","state"=>2]);
								}
							}else{
								 
								//错误终止
								return json_encode(["msg"=>"系统繁忙请稍后再试","state"=>2]);
							}
						}else{
							
							return json_encode(["msg"=>$qudao."必须填写","state"=>2]);
						}
						
						break;
					case 'client_id':
					#客户
					//
						if($content){
							if($client=="经销商"){

								$tmpclient=Db::table("cg_jxs")->alias("j")
								->join("cg_market_zoning z","j.zoning=z.id")
								->join("cg_market_provice p","j.provice=p.id")
								->join("cg_market_city c","j.city=c.id")
								->where(["j.title"=>$content,"j.isdel"=>0])->field("j.id,z.id as zid,p.id as pid,c.id as cid")->find();

								if($tmpclient){
									$adddata["jingxiao"]=$tmpclient["id"];
									$adddata["fenqu"]=$tmpclient["zid"];
									$adddata["sheng"]=$tmpclient["pid"];
									$adddata["shi"]=$tmpclient["cid"];
								}else{
									 
									return json_encode(["msg"=>"经销商不存在或已删除","state"=>2]);
								}
							}else if($client=="系统"){
								$tmpclient=Db::table("cg_system")->alias("s")
								->join("cg_jxs j","s.jxs=j.id")
								->join("cg_market_zoning z","j.zoning=z.id")
								->join("cg_market_provice p","j.provice=p.id")
								->join("cg_market_city c","j.city=c.id")
								->where(["s.title"=>$content,"s.isdel"=>0,"j.isdel"=>0])
								->field("j.id as jxs,s.id as xitong,z.id as zid,p.id as pid,c.id as cid")->find();
								if($tmpclient){
									$adddata["jingxiao"]=$tmpclient["jxs"];
									$adddata["xitong"]=$tmpclient["xitong"];
									$adddata["fenqu"]=$tmpclient["zid"];
									$adddata["sheng"]=$tmpclient["pid"];
									$adddata["shi"]=$tmpclient["cid"];
								}else{
									 
									return json_encode(["msg"=>"系统不存或所属经销商已删除","state"=>2]);
								}
							}else if($client=="网点"){
								$tmpclient=Db::table("cg_system")->alias("s")
								->join("cg_jxs j","s.jxs=j.id")
								->join("cg_net n","n.system_id=s.id")
								->join("cg_market_zoning z","j.zoning=z.id")
								->join("cg_market_provice p","j.provice=p.id")
								->join("cg_market_city c","j.city=c.id")
								->where(["n.title"=>$content,"n.isdel"=>0,"j.isdel"=>0,"s.isdel"=>0])
								->field("j.id as jxs,s.id as xitong,n.id as net,z.id as zid,p.id as pid,c.id as cid")->find();
								if($tmpclient){
									$adddata["jingxiao"]=$tmpclient["jxs"];
									$adddata["xitong"]=$tmpclient["xitong"];
									$adddata["wangdian"]=$tmpclient["net"];
									$adddata["fenqu"]=$tmpclient["zid"];
									$adddata["sheng"]=$tmpclient["pid"];
									$adddata["shi"]=$tmpclient["cid"];
								}else{
									 
									return json_encode(["msg"=>"网点不存或所属经销商，系统已删除","state"=>2]);
								}
							}else{
								//错误终止
								
								return json_encode(["msg"=>"系统繁忙请稍后再试","state"=>2]);
							}
						}else{
							
							return json_encode(["msg"=>$client."必须填写","state"=>2]);
						}
					break;
					case 'product_id':
					#产品纬度
						if($content){
							if($product=="品牌"){
								$tmppro=Db::table("pro_brands")->where(["is_actived"=>1,"is_deleted"=>0,"brand_name"=>$content])
								->field("brand_name,id")->find();
								if($tmppro){
									$adddata["pinpai"]=$tmppro["id"];
								}else{
									
									return json_encode(["msg"=>"品牌不存或已删除","state"=>2]);
								}

							}else if($product=="品类"){
								$tmppro=Db::table("pro_category")->alias("c")
								->join("pro_brands b","c.brand_id=b.id")->where(["c.is_actived"=>1,"c.is_deleted"=>0,"c.category_name"=>$content,"b.is_actived"=>1,"b.is_deleted"=>0])
								->field("b.id as brand_name,c.id as category_name")->find();
								if($tmppro){
									$adddata["pinpai"]=$tmppro["brand_name"];
									$adddata["pinlei"]=$tmppro["category_name"];
								}else{
									
									return json_encode(["msg"=>"品类不存或所属品牌已删除","state"=>2]);
								}
							}else if($product=="系列"){
								$tmppro=Db::table("pro_series")->alias("s")
								->join("pro_category c","s.category_id=c.id")
								->join("pro_brands b","c.brand_id=b.id")->where(["c.is_actived"=>1,"c.is_deleted"=>0,"s.series_name"=>$content,"b.is_actived"=>1,"b.is_deleted"=>0,"s.is_actived"=>1,"s.is_deleted"=>0])
								->field("b.id as brand_name,c.id as category_name,s.id as series_name")->find();
								if($tmppro){
									$adddata["pinpai"]=$tmppro["brand_name"];
									$adddata["pinlei"]=$tmppro["category_name"];
									$adddata["xilie"]=$tmppro["series_name"];
								}else{
									
									return json_encode(["msg"=>"系列不存或所属品牌或品类已删除","state"=>2]);
								}
							}else{
								//错误终止
								// Db::rollBack();
								return json_encode(["msg"=>"系统繁忙请稍后再试","state"=>2]);
							}
						}else{
							 
							return json_encode(["msg"=>$product."必须填写","state"=>2]);
						}
					break;
					case 'area_id':
					#区域纬度
					if($content){

						if($area=="大区"){
							$tmparea=Db::table("cg_market_zoning")->where(["isdel"=>0,"title"=>$content])->field("id as title")->find();
							if($tmparea){
								$adddata["fenqu"]=$tmparea["title"];
							}else{
								
								return json_encode(["msg"=>"大区不存在或已删除","state"=>2]);
							}
						}else if($area=="省份"){
							$tmparea=Db::table("cg_market_zoning")->alias("z")
							->join("cg_market_provice p","p.zoning=z.id")
							->where(["p.isdel"=>0,"z.isdel"=>0,"p.title"=>$content])->field("z.id as ztitle,p.id as ptitle")->find();
							if($tmparea){
								$adddata["fenqu"]=$tmparea["ztitle"];
								$adddata["sheng"]=$tmparea["ptitle"];
							}else{
								 
								return json_encode(["msg"=>"省份不存在或上级大区已删除","state"=>2]);
							}
						}else if($area=="城市"){
							$tmparea=Db::table("cg_market_zoning")->alias("z")
							->join("cg_market_provice p","p.zoning=z.id")
							->join("cg_market_city c","c.provice=p.id")
							->where(["p.isdel"=>0,"z.isdel"=>0,"c.isdel"=>0,"c.title"=>$content])->field("z.id as ztitle,p.id as ptitle,c.id as ctitle")->find();
							if($tmparea){
								$adddata["fenqu"]=$tmparea["ztitle"];
								$adddata["sheng"]=$tmparea["ptitle"];
								$adddata["shi"]=$tmparea["ctitle"];
							}else{
								 
								return json_encode(["msg"=>"城市不存在或上级省份,大区已删除","state"=>2]);
							}
						}else{
							
							return json_encode(["msg"=>"系统繁忙请稍后再试","state"=>2]);
						}
					}else{
						 
						return json_encode(["msg"=>$area."必须填写","state"=>2]);
					}

					break;
					case 'feiyong_id':
					#费用纬度
					if($content){
						if($feiyong=="一级费用科目"){
							$tmpfeiyong=Db::table("cg_feiyongkemu1")->where(["title"=>$content])->field("id as title1")->find();
							if($tmpfeiyong){
								$adddata["yijifeiyong"]=$tmpfeiyong["title1"];
							}else{
								
								return json_encode(["msg"=>"一级费用科目不存在或已删除","state"=>2]);
							}
						}else if($feiyong=="二级费用科目"){
							$tmpfeiyong=Db::table("cg_feiyongkemu1")->alias("f1")
							->join("cg_feiyongkemu2 f2","f2.pid=f1.id")
							->where(["f2.title"=>$content])->field("f1.id as title1,f2.id as title2")->find();
							if($tmpfeiyong){
								$adddata["yijifeiyong"]=$tmpfeiyong["title1"];
								$adddata["erjifeiyong"]=$tmpfeiyong["title2"];
							}else{
								
								return json_encode(["msg"=>"二级费用科目不存在或已删除","state"=>2]);
							}
						}else if($feiyong=="三级费用科目"){
							$tmpfeiyong=Db::table("cg_feiyongkemu1")->alias("f1")
							->join("cg_feiyongkemu2 f2","f2.pid=f1.id")
							->join("cg_feiyongkemu3 f3","f3.pid=f2.id")
							->where(["f3.title"=>$content])->field("f1.id as title1,f2.id as title2,f3.id as title3")->find();
							if($tmpfeiyong){
								$adddata["yijifeiyong"]=$tmpfeiyong["title1"];
								$adddata["erjifeiyong"]=$tmpfeiyong["title2"];
								$adddata["sanjifeiyong"]=$tmpfeiyong["title3"];
							}else{
								
								return json_encode(["msg"=>"三级费用科目不存在或已删除","state"=>2]);
							}
						}else{
							 
							return json_encode(["msg"=>"系统繁忙请稍后再试","state"=>2]);
						}
					}else{
						
						return json_encode(["msg"=>$feiyong."必须填写","state"=>2]);
					}
					break;
					case "cainian":
					//财年
					if($content){
						$tmpcainian=Db::table("cg_cainian")->where(["title"=>$content])->find();
						if($tmpcainian){
							$adddata["cainian"]=$tmpcainian["id"];
						}else{
							 
							return json_encode(["msg"=>"财年不存在","state"=>2]);
						}
						
					}else{
						
						return json_encode(["msg"=>"财年必须填写","state"=>2]);
					}
					break;
					case "kaoheqi":
					#考核期
					if($content){
						if($kaoheqi=="一级考核期"){
							$tmpkaohe=Db::table("cg_kaoheqi1")->alias("k1")
							->join("cg_cainian c","k1.cid=c.id")->where(["k1.title"=>$content,"c.id"=>$adddata["cainian"]])->field("k1.id as title1")->find();
							if($tmpkaohe){
								$adddata["yijikaoheqi"]=$tmpkaohe["title1"];
							}else{
								 
								return json_encode(["msg"=>$kaoheqi."不存在或财年不符","state"=>2]);
							}
						}else if($kaoheqi=="二级考核期"){
							//return json_encode(["msg"=>$content,"state"=>2]);
							$tmpkaohe=Db::table("cg_kaoheqi1")->alias("k1")->join("cg_kaoheqi2 k2","k2.pid=k1.id")
							->join("cg_cainian c","k1.cid=c.id")->where(["k2.title"=>$content,"c.id"=>$adddata["cainian"]])->field("k1.id as title1,k2.id as title2")->find();
							if($tmpkaohe){
								$adddata["yijikaoheqi"]=$tmpkaohe["title1"];
								$adddata["erjikaoheqi"]=$tmpkaohe["title2"];
							}else{
								
								return json_encode(["msg"=>$kaoheqi."不存在或财年不符","state"=>2]);
							}
						}else{
							
							return json_encode(["msg"=>"系统繁忙请稍后再试","state"=>2]);
						}
					}else{
						
						return json_encode(["msg"=>$kaoheqi."必须填写","state"=>2]);
					}
					break;
					case 'people_id':
					if($content){
						$eid=db("employee")->where("em_name='$content'")->value("id");
						if($eid)
							$adddata["people"]=$eid;
						else
							return json_encode(["msg"=>"人员不存在","state"=>2]);
					}else{
						
						return json_encode(["msg"=>"人员必须填写","state"=>2]);
					}
					break;
					case 'sale_id':
					if($content){
						$adddata["sale"]=$content;
					}else{
						 
						return json_encode(["msg"=>"毛销售额必须填写","state"=>2]);
					}
					break;
					case 'sale_real_id':
					if($content){
						$adddata["sale_real"]=$content;
					}else{
						
						return json_encode(["msg"=>"净销售额必须填写","state"=>2]);
					}
					break;
					case 'profit_id':
					if($content){
						$adddata["profit"]=$content;
					}else{
						 
						return json_encode(["msg"=>"利润目标必须填写","state"=>2]);
					}
					break;
					case 'depart_id':
					if($content){
						$tmpdep=Db::table("department")->where(["de_name"=>$content,"is_actived"=>1,"is_deleted"=>0])->find();
						if($tmpdep){
							$adddata["depart"]=$tmpdep["id"];
						}else{
							 
							return json_encode(["msg"=>"部门不存在或已删除","state"=>2]);
						}
					}else{
						
						return json_encode(["msg"=>"部门必须填写","state"=>2]);
					}
					break;
					case 'yusuan':
					if($content){
						$adddata["feiyong"]=$content;
					}else{
						
						return json_encode(["msg"=>"预算必须填写","state"=>2]);
					}
					break;
					default:
						# code...
						break;
				}
				
				//$dataset[] = $sheet->getCell($c.$row)->getValue();
				//echo $c.$row.":".$sheet->getCell($c.$row)->getValue()."<br />";
			}
			$alldata[]=$adddata;
		}
		  Db::startTrans();
       try{
			$res2=Db::table("target_apply_detail")->where(["apply_id"=>$tid])->delete();
			for($i=0;$i<count($alldata);$i++){
				$alldata[$i]["apply_id"]=$tid;
			}
			$res1=Db::table("target_apply_detail")->insertAll($alldata);
			//$res3=Db::table("target_apply")->where(["id"=>$tid])->update(["tag"=>2,"nextid"=>$res2]);
		}catch(\Exception $e){

                Db::rollBack();
                return json_encode(["msg"=>"系统繁忙请稍后再试","state"=>2]);
            }
            if($res1&&$res2){
                Db::commit(); //事物提交
               return json_encode(["msg"=>"操作已成功","state"=>1]);
            }else{
                Db::rollBack();
                return json_encode(["msg"=>"系统繁忙请稍后再试","state"=>2]);
            }
		//print_r($alldata);
	}

	public function excel_yidong($title,$tid,$state,$zhuti,$remark,$filename,$fujian=null){
		//$title="AP费用预算格式";
		Vendor("PHPExcel");
        Vendor("PHPExcel.IOFactory");
        
       	Vendor("PHPExcel.Reader.Excel5");
       	//$filename=ROOT_PATH . 'public/a.xls';
       	$objReader = PHPExcel_IOFactory::createReaderForFile($filename);
		$objPHPExcel = $objReader->load($filename);
		$sheet=$objPHPExcel->setActiveSheetIndex(0);
		$highestRow = $sheet->getHighestRow(); // 取得总行数
		$highestColumm = $sheet->getHighestColumn(); // 取得总列数
		//获取基本格式
		$target=Db::table("target_format")->alias("tf")->join("target_format_type tft","tf.type_id=tft.id")
        ->where(["tft.format_type"=>$title,"tf.is_actived"=>1,"tf.is_deleted"=>0])->find();

        $datatianxie=[];
        if($target["channel_id"]){
        	$datatianxie[]="channel_id";
        	$tmp=Db::table("field")->where(["id"=>$target["channel_id"]])->find();
        	$qudao=$tmp["field_name"];
        }
        if($target["client_id"]){
        	$datatianxie[]="client_id";
        	$tmp=Db::table("field")->where(["id"=>$target["client_id"]])->find();
        	$client=$tmp["field_name"];
        }
        if($target["product_id"]){
        	$datatianxie[]="product_id";
        	$tmp=Db::table("field")->where(["id"=>$target["product_id"]])->find();
        	$product=$tmp["field_name"];
        }
        if($target["area_id"]){
        	$datatianxie[]="area_id";
        	$tmp=Db::table("field")->where(["id"=>$target["area_id"]])->find();
        	$area=$tmp["field_name"];
        }
        if($target["feiyong_id"]){
        	$datatianxie[]="feiyong_id";
        	$tmp=Db::table("field")->where(["id"=>$target["feiyong_id"]])->find();
        	$feiyong=$tmp["field_name"];
        }

       
        	
       
        if($target["kaoheqi"]){
        	$datatianxie[]="cainian";
        	$datatianxie[]="kaoheqi";
        	$tmp=Db::table("field")->where(["id"=>$target["kaoheqi"]])->find();
        	$kaoheqi=$tmp["field_name"];
        }
        if($target["people_id"]){
        	$datatianxie[]="people_id";
        }
        if($target["sale_id"]){
        	$datatianxie[]="sale_id";
        }
        if($target["sale_real_id"]){
        	$datatianxie[]="sale_real_id";
        }
        if($target["profit_id"]){
        	$datatianxie[]="profit_id";
        }
        if($target["depart_id"]){
        	$datatianxie[]="depart_id";
        }
        if($title!="集团目标格式"){
        	 $datatianxie[]="yusuan";
        }
       
       // print_r($datatianxie);exit;
        //$
       $alldata=[];
       $timenow=date("Ymd");
       $num=Db::table("target_apply")->where("no like '$timenow%'")->count();
       $num++;
       $strnum=$timenow.sprintf("%05d", $num);
     
       	$datata=["type_id"=>$target["type_id"],"c_users_id"=>Session::get("login_id"),"created_on"=>date("Y-m-d H:i:s"),"state"=>$state,"title"=>$zhuti,"remark"=>$remark,"path"=>$filename,"no"=>$strnum,"fujian"=>$fujian,"state2"=>2,"yuanid"=>$tid];
       	
       	$conunt_de=0;
        for ($row = 3; $row <= $highestRow; $row++){//行数是以第1行开始
        	if(!trim($sheet->getCell('A'.$row)->getValue())){
        		break;
        	}
        	$conunt_de++;
        	$strcountde=sprintf("%03d",$conunt_de);
        	 $adddata=["detail_no"=>$strcountde];
			for ($column = 0; $column < count($datatianxie); $column++) {//列数是以A列开始
				$c=chr($column+65);//echo $c;exit;
				$content=trim($sheet->getCell($c.$row)->getValue());
				switch ($datatianxie[$column]) {
					case 'channel_id':
						# code...渠道编号
						if($content){
							if($qudao=="一级渠道"){
							$tmpqudao=Db::table("cg_channel_types1")->where(["title"=>$content])->field("id,title")->find();
								if($tmpqudao){
									$adddata["channel1"]=$tmpqudao["id"];
								}else{
									
									return json_encode(["msg"=>"一级渠道不存在或已删除","state"=>2]);
								}
							}else if($qudao=="二级渠道"){
								$tmpqudao=Db::table("cg_channel_types2")->alias("c2")
								->join("cg_channel_types1 c1","c2.pid=c1.id")
								->where(["c2.title"=>$content])
								->field("c1.id as title1,c2.id as title2")
								->find();
								if($tmpqudao){
									$adddata["channel1"]=$tmpqudao["title1"];
									$adddata["channel2"]=$tmpqudao["title2"];
								}else{
									
									return json_encode(["msg"=>"二级渠道不存在或上级渠道已删除","state"=>2]);
								}
							}else{
								 
								//错误终止
								return json_encode(["msg"=>"系统繁忙请稍后再试","state"=>2]);
							}
						}else{
							
							return json_encode(["msg"=>$qudao."必须填写","state"=>2]);
						}
						
						break;
					case 'client_id':
					#客户
					//
						if($content){
							if($client=="经销商"){

								$tmpclient=Db::table("cg_jxs")->alias("j")
								->join("cg_market_zoning z","j.zoning=z.id")
								->join("cg_market_provice p","j.provice=p.id")
								->join("cg_market_city c","j.city=c.id")
								->where(["j.title"=>$content,"j.isdel"=>0])->field("j.id,z.id as zid,p.id as pid,c.id as cid")->find();

								if($tmpclient){
									$adddata["jingxiao"]=$tmpclient["id"];
									$adddata["fenqu"]=$tmpclient["zid"];
									$adddata["sheng"]=$tmpclient["pid"];
									$adddata["shi"]=$tmpclient["cid"];
								}else{
									 
									return json_encode(["msg"=>"经销商不存在或已删除","state"=>2]);
								}
							}else if($client=="系统"){
								$tmpclient=Db::table("cg_system")->alias("s")
								->join("cg_jxs j","s.jxs=j.id")
								->join("cg_market_zoning z","j.zoning=z.id")
								->join("cg_market_provice p","j.provice=p.id")
								->join("cg_market_city c","j.city=c.id")
								->where(["s.title"=>$content,"s.isdel"=>0,"j.isdel"=>0])
								->field("j.id as jxs,s.id as xitong,z.id as zid,p.id as pid,c.id as cid")->find();
								if($tmpclient){
									$adddata["jingxiao"]=$tmpclient["jxs"];
									$adddata["xitong"]=$tmpclient["xitong"];
									$adddata["fenqu"]=$tmpclient["zid"];
									$adddata["sheng"]=$tmpclient["pid"];
									$adddata["shi"]=$tmpclient["cid"];
								}else{
									 
									return json_encode(["msg"=>"系统不存或所属经销商已删除","state"=>2]);
								}
							}else if($client=="网点"){
								$tmpclient=Db::table("cg_system")->alias("s")
								->join("cg_jxs j","s.jxs=j.id")
								->join("cg_net n","n.system_id=s.id")
								->join("cg_market_zoning z","j.zoning=z.id")
								->join("cg_market_provice p","j.provice=p.id")
								->join("cg_market_city c","j.city=c.id")
								->where(["n.title"=>$content,"n.isdel"=>0,"j.isdel"=>0,"s.isdel"=>0])
								->field("j.id as jxs,s.id as xitong,n.id as net,z.id as zid,p.id as pid,c.id as cid")->find();
								if($tmpclient){
									$adddata["jingxiao"]=$tmpclient["jxs"];
									$adddata["xitong"]=$tmpclient["xitong"];
									$adddata["wangdian"]=$tmpclient["net"];
									$adddata["fenqu"]=$tmpclient["zid"];
									$adddata["sheng"]=$tmpclient["pid"];
									$adddata["shi"]=$tmpclient["cid"];
								}else{
									 
									return json_encode(["msg"=>"网点不存或所属经销商，系统已删除","state"=>2]);
								}
							}else{
								//错误终止
								
								return json_encode(["msg"=>"系统繁忙请稍后再试","state"=>2]);
							}
						}else{
							
							return json_encode(["msg"=>$client."必须填写","state"=>2]);
						}
					break;
					case 'product_id':
					#产品纬度
						if($content){
							if($product=="品牌"){
								$tmppro=Db::table("pro_brands")->where(["is_actived"=>1,"is_deleted"=>0,"brand_name"=>$content])
								->field("brand_name,id")->find();
								if($tmppro){
									$adddata["pinpai"]=$tmppro["id"];
								}else{
									
									return json_encode(["msg"=>"品牌不存或已删除","state"=>2]);
								}

							}else if($product=="品类"){
								$tmppro=Db::table("pro_category")->alias("c")
								->join("pro_brands b","c.brand_id=b.id")->where(["c.is_actived"=>1,"c.is_deleted"=>0,"c.category_name"=>$content,"b.is_actived"=>1,"b.is_deleted"=>0])
								->field("b.id as brand_name,c.id as category_name")->find();
								if($tmppro){
									$adddata["pinpai"]=$tmppro["brand_name"];
									$adddata["pinlei"]=$tmppro["category_name"];
								}else{
									
									return json_encode(["msg"=>"品类不存或所属品牌已删除","state"=>2]);
								}
							}else if($product=="系列"){
								$tmppro=Db::table("pro_series")->alias("s")
								->join("pro_category c","s.category_id=c.id")
								->join("pro_brands b","c.brand_id=b.id")->where(["c.is_actived"=>1,"c.is_deleted"=>0,"s.series_name"=>$content,"b.is_actived"=>1,"b.is_deleted"=>0,"s.is_actived"=>1,"s.is_deleted"=>0])
								->field("b.id as brand_name,c.id as category_name,s.id as series_name")->find();
								if($tmppro){
									$adddata["pinpai"]=$tmppro["brand_name"];
									$adddata["pinlei"]=$tmppro["category_name"];
									$adddata["xilie"]=$tmppro["series_name"];
								}else{
									
									return json_encode(["msg"=>"系列不存或所属品牌或品类已删除","state"=>2]);
								}
							}else{
								//错误终止
								// Db::rollBack();
								return json_encode(["msg"=>"系统繁忙请稍后再试","state"=>2]);
							}
						}else{
							 
							return json_encode(["msg"=>$product."必须填写","state"=>2]);
						}
					break;
					case 'area_id':
					#区域纬度
					if($content){

						if($area=="大区"){
							$tmparea=Db::table("cg_market_zoning")->where(["isdel"=>0,"title"=>$content])->field("id as title")->find();
							if($tmparea){
								$adddata["fenqu"]=$tmparea["title"];
							}else{
								
								return json_encode(["msg"=>"大区不存在或已删除","state"=>2]);
							}
						}else if($area=="省份"){
							$tmparea=Db::table("cg_market_zoning")->alias("z")
							->join("cg_market_provice p","p.zoning=z.id")
							->where(["p.isdel"=>0,"z.isdel"=>0,"p.title"=>$content])->field("z.id as ztitle,p.id as ptitle")->find();
							if($tmparea){
								$adddata["fenqu"]=$tmparea["ztitle"];
								$adddata["sheng"]=$tmparea["ptitle"];
							}else{
								 
								return json_encode(["msg"=>"省份不存在或上级大区已删除","state"=>2]);
							}
						}else if($area=="城市"){
							$tmparea=Db::table("cg_market_zoning")->alias("z")
							->join("cg_market_provice p","p.zoning=z.id")
							->join("cg_market_city c","c.provice=p.id")
							->where(["p.isdel"=>0,"z.isdel"=>0,"c.isdel"=>0,"c.title"=>$content])->field("z.id as ztitle,p.id as ptitle,c.id as ctitle")->find();
							if($tmparea){
								$adddata["fenqu"]=$tmparea["ztitle"];
								$adddata["sheng"]=$tmparea["ptitle"];
								$adddata["shi"]=$tmparea["ctitle"];
							}else{
								 
								return json_encode(["msg"=>"城市不存在或上级省份,大区已删除","state"=>2]);
							}
						}else{
							
							return json_encode(["msg"=>"系统繁忙请稍后再试","state"=>2]);
						}
					}else{
						 
						return json_encode(["msg"=>$area."必须填写","state"=>2]);
					}

					break;
					case 'feiyong_id':
					#费用纬度
					if($content){
						if($feiyong=="一级费用科目"){
							$tmpfeiyong=Db::table("cg_feiyongkemu1")->where(["title"=>$content])->field("id as title1")->find();
							if($tmpfeiyong){
								$adddata["yijifeiyong"]=$tmpfeiyong["title1"];
							}else{
								
								return json_encode(["msg"=>"一级费用科目不存在或已删除","state"=>2]);
							}
						}else if($feiyong=="二级费用科目"){
							$tmpfeiyong=Db::table("cg_feiyongkemu1")->alias("f1")
							->join("cg_feiyongkemu2 f2","f2.pid=f1.id")
							->where(["f2.title"=>$content])->field("f1.id as title1,f2.id as title2")->find();
							if($tmpfeiyong){
								$adddata["yijifeiyong"]=$tmpfeiyong["title1"];
								$adddata["erjifeiyong"]=$tmpfeiyong["title2"];
							}else{
								
								return json_encode(["msg"=>"二级费用科目不存在或已删除","state"=>2]);
							}
						}else if($feiyong=="三级费用科目"){
							$tmpfeiyong=Db::table("cg_feiyongkemu1")->alias("f1")
							->join("cg_feiyongkemu2 f2","f2.pid=f1.id")
							->join("cg_feiyongkemu3 f3","f3.pid=f2.id")
							->where(["f3.title"=>$content])->field("f1.id as title1,f2.id as title2,f3.id as title3")->find();
							if($tmpfeiyong){
								$adddata["yijifeiyong"]=$tmpfeiyong["title1"];
								$adddata["erjifeiyong"]=$tmpfeiyong["title2"];
								$adddata["sanjifeiyong"]=$tmpfeiyong["title3"];
							}else{
								
								return json_encode(["msg"=>"三级费用科目不存在或已删除","state"=>2]);
							}
						}else{
							 
							return json_encode(["msg"=>"系统繁忙请稍后再试","state"=>2]);
						}
					}else{
						
						return json_encode(["msg"=>$feiyong."必须填写","state"=>2]);
					}
					break;
					case "cainian":
					//财年
					if($content){
						$tmpcainian=Db::table("cg_cainian")->where(["title"=>$content])->find();
						if($tmpcainian){
							$adddata["cainian"]=$tmpcainian["id"];
						}else{
							 
							return json_encode(["msg"=>"财年不存在","state"=>2]);
						}
						
					}else{
						
						return json_encode(["msg"=>"财年必须填写","state"=>2]);
					}
					break;
					case "kaoheqi":
					#考核期
					if($content){
						if($kaoheqi=="一级考核期"){
							$tmpkaohe=Db::table("cg_kaoheqi1")->alias("k1")
							->join("cg_cainian c","k1.cid=c.id")->where(["k1.title"=>$content,"c.id"=>$adddata["cainian"]])->field("k1.id as title1")->find();
							if($tmpkaohe){
								$adddata["yijikaoheqi"]=$tmpkaohe["title1"];
							}else{
								 
								return json_encode(["msg"=>$kaoheqi."不存在或财年不符","state"=>2]);
							}
						}else if($kaoheqi=="二级考核期"){
							//return json_encode(["msg"=>$content,"state"=>2]);
							$tmpkaohe=Db::table("cg_kaoheqi1")->alias("k1")->join("cg_kaoheqi2 k2","k2.pid=k1.id")
							->join("cg_cainian c","k1.cid=c.id")->where(["k2.title"=>$content,"c.id"=>$adddata["cainian"]])->field("k1.id as title1,k2.id as title2")->find();
							if($tmpkaohe){
								$adddata["yijikaoheqi"]=$tmpkaohe["title1"];
								$adddata["erjikaoheqi"]=$tmpkaohe["title2"];
							}else{
								
								return json_encode(["msg"=>$kaoheqi."不存在或财年不符","state"=>2]);
							}
						}else{
							
							return json_encode(["msg"=>"系统繁忙请稍后再试","state"=>2]);
						}
					}else{
						
						return json_encode(["msg"=>$kaoheqi."必须填写","state"=>2]);
					}
					break;
					case 'people_id':
					if($content){
						$eid=db("employee")->where("em_name='$content'")->value("id");
						if($eid)
							$adddata["people"]=$eid;
						else
							return json_encode(["msg"=>"人员不存在","state"=>2]);
					}else{
						
						return json_encode(["msg"=>"人员必须填写","state"=>2]);
					}
					break;
					case 'sale_id':
					if($content){
						$adddata["sale"]=$content;
					}else{
						 
						return json_encode(["msg"=>"毛销售额必须填写","state"=>2]);
					}
					break;
					case 'sale_real_id':
					if($content){
						$adddata["sale_real"]=$content;
					}else{
						
						return json_encode(["msg"=>"净销售额必须填写","state"=>2]);
					}
					break;
					case 'profit_id':
					if($content){
						$adddata["profit"]=$content;
					}else{
						 
						return json_encode(["msg"=>"利润目标必须填写","state"=>2]);
					}
					break;
					case 'depart_id':
					if($content){
						$tmpdep=Db::table("department")->where(["de_name"=>$content,"is_actived"=>1,"is_deleted"=>0])->find();
						if($tmpdep){
							$adddata["depart"]=$tmpdep["id"];
						}else{
							 
							return json_encode(["msg"=>"部门不存在或已删除","state"=>2]);
						}
					}else{
						
						return json_encode(["msg"=>"部门必须填写","state"=>2]);
					}
					break;
					case 'yusuan':
					if($content){
						$adddata["feiyong"]=$content;
					}else{
						
						return json_encode(["msg"=>"预算必须填写","state"=>2]);
					}
					break;
					default:
						# code...
						break;
				}
				
				//$dataset[] = $sheet->getCell($c.$row)->getValue();
				//echo $c.$row.":".$sheet->getCell($c.$row)->getValue()."<br />";
			}
			$alldata[]=$adddata;
		}
		//return count($alldata);
		  Db::startTrans();
       try{
			$res2=Db::table("target_apply")->insertGetId($datata);
			for($i=0;$i<count($alldata);$i++){
				$alldata[$i]["apply_id"]=$res2;
			}
			$res1=Db::table("target_apply_detail")->insertAll($alldata);
			$geti=Db::table("target_apply")->where(["id"=>$tid])->field("tag,nextid")->find();
			if($geti["tag"]&&$geti["nextid"]){
				Db::table("target_apply")->where(["id"=>$geti["nextid"]])->delete();
				Db::table("target_apply_detail")->where(["apply_id"=>$geti["nextid"]])->delete();
			}
			Db::table("target_apply")->where(["id"=>$tid])->update(["tag"=>2,"nextid"=>$res2]);
		}catch(\Exception $e){

                Db::rollBack();
                return json_encode(["msg"=>"系统繁忙请稍后再试","state"=>2]);
            }
            if($res1&&$res2){
            	if($state==3){
            		$backjson=$this->ystijiao($res2);
            		Db::table("target_apply")->where(["id"=>$tid])->update(["tag"=>2,"nextid"=>$res2,"islock"=>1]);
            		Db::commit();
            		return json_encode(["msg"=>"操作已成功","state"=>1]);
            	}else{
            		Db::table("target_apply")->where(["id"=>$tid])->update(["tag"=>2,"nextid"=>$res2]);
            		Db::commit(); //事物提交
               		return json_encode(["msg"=>"操作已成功","state"=>1]);
            	}
               
            }else{
                Db::rollBack();
                return json_encode(["msg"=>"系统繁忙请稍后再试12345","state"=>2]);
            }
		//print_r($alldata);
	}

	public function excel_duqu($title,$state,$zhuti,$remark,$filename,$fujian=null){
		//$title="AP费用预算格式";
		Vendor("PHPExcel");
        Vendor("PHPExcel.IOFactory");
        
       	Vendor("PHPExcel.Reader.Excel5");
       	//$filename=ROOT_PATH . 'public/a.xls';
       	$objReader = PHPExcel_IOFactory::createReaderForFile($filename);
		$objPHPExcel = $objReader->load($filename);
		$sheet=$objPHPExcel->setActiveSheetIndex(0);
		$highestRow = $sheet->getHighestRow(); // 取得总行数
		$highestColumm = $sheet->getHighestColumn(); // 取得总列数
		//获取基本格式
		$target=Db::table("target_format")->alias("tf")->join("target_format_type tft","tf.type_id=tft.id")
        ->where(["tft.format_type"=>$title,"tf.is_actived"=>1,"tf.is_deleted"=>0])->find();

        $datatianxie=[];
        if($target["channel_id"]){
        	$datatianxie[]="channel_id";
        	$tmp=Db::table("field")->where(["id"=>$target["channel_id"]])->find();
        	$qudao=$tmp["field_name"];
        }
        if($target["client_id"]){
        	$datatianxie[]="client_id";
        	$tmp=Db::table("field")->where(["id"=>$target["client_id"]])->find();
        	$client=$tmp["field_name"];
        }
        if($target["product_id"]){
        	$datatianxie[]="product_id";
        	$tmp=Db::table("field")->where(["id"=>$target["product_id"]])->find();
        	$product=$tmp["field_name"];
        }
        if($target["area_id"]){
        	$datatianxie[]="area_id";
        	$tmp=Db::table("field")->where(["id"=>$target["area_id"]])->find();
        	$area=$tmp["field_name"];
        }
        if($target["feiyong_id"]){
        	$datatianxie[]="feiyong_id";
        	$tmp=Db::table("field")->where(["id"=>$target["feiyong_id"]])->find();
        	$feiyong=$tmp["field_name"];
        }

       
        	
       
        if($target["kaoheqi"]){
        	$datatianxie[]="cainian";
        	$datatianxie[]="kaoheqi";
        	$tmp=Db::table("field")->where(["id"=>$target["kaoheqi"]])->find();
        	$kaoheqi=$tmp["field_name"];
        }
        if($target["people_id"]){
        	$datatianxie[]="people_id";
        }
        if($target["sale_id"]){
        	$datatianxie[]="sale_id";
        }
        if($target["sale_real_id"]){
        	$datatianxie[]="sale_real_id";
        }
        if($target["profit_id"]){
        	$datatianxie[]="profit_id";
        }
        if($target["depart_id"]){
        	$datatianxie[]="depart_id";
        }
        if($title!="集团目标格式"){
        	 $datatianxie[]="yusuan";
        }
       
       // print_r($datatianxie);exit;
        //$
       $alldata=[];
       $timenow=date("Ymd");
       $num=Db::table("target_apply")->where("no like '$timenow%'")->count();
       $num++;
       $strnum=$timenow.sprintf("%05d", $num);
        $pizhunu=Session::get("login_id");
     
       

       	$datata=["type_id"=>$target["type_id"],"c_users_id"=>Session::get("login_id"),"created_on"=>date("Y-m-d H:i:s"),"state"=>1,"title"=>$zhuti,"remark"=>$remark,"path"=>$filename,"no"=>$strnum,"fujian"=>$fujian];
       	
       	$conunt_de=0;
       	//echo $highestRow;exit;
        for ($row = 3; $row <= $highestRow; $row++){//行数是以第1行开始

        	if(!trim($sheet->getCell('A'.$row)->getValue())){
        		break;
        	}
        	$conunt_de++;
        	$strcountde=sprintf("%03d",$conunt_de);
        	 $adddata=["detail_no"=>$strcountde];
			for ($column = 0; $column < count($datatianxie); $column++) {//列数是以A列开始
				$c=chr($column+65);//echo $c;exit;
				$content=trim($sheet->getCell($c.$row)->getValue());
				
				switch ($datatianxie[$column]) {
					case 'channel_id':
						# code...渠道编号
						if($content){
							if($qudao=="一级渠道"){
							$tmpqudao=Db::table("cg_channel_types1")->where(["title"=>$content])->field("id,title")->find();
								if($tmpqudao){
									$adddata["channel1"]=$tmpqudao["id"];
								}else{
									
									return json_encode(["msg"=>"一级渠道不存在或已删除","state"=>2]);
								}
							}else if($qudao=="二级渠道"){
								$tmpqudao=Db::table("cg_channel_types2")->alias("c2")
								->join("cg_channel_types1 c1","c2.pid=c1.id")
								->where(["c2.title"=>$content])
								->field("c1.id as title1,c2.id as title2")
								->find();
								if($tmpqudao){
									$adddata["channel1"]=$tmpqudao["title1"];
									$adddata["channel2"]=$tmpqudao["title2"];
								}else{
									
									return json_encode(["msg"=>"二级渠道不存在或上级渠道已删除","state"=>2]);
								}
							}else{
								 
								//错误终止
								return json_encode(["msg"=>"系统繁忙请稍后再试","state"=>2]);
							}
						}else{
							
							return json_encode(["msg"=>$qudao."必须填写","state"=>2]);
						}
						
						break;
					case 'client_id':
					#客户
					//
						if($content){
							if($client=="经销商"){

								$tmpclient=Db::table("cg_jxs")->alias("j")
								->join("cg_market_zoning z","j.zoning=z.id")
								->join("cg_market_provice p","j.provice=p.id")
								->join("cg_market_city c","j.city=c.id")
								->where(["j.title"=>$content,"j.isdel"=>0])->field("j.id,z.id as zid,p.id as pid,c.id as cid")->find();

								if($tmpclient){
									$adddata["jingxiao"]=$tmpclient["id"];
									$adddata["fenqu"]=$tmpclient["zid"];
									$adddata["sheng"]=$tmpclient["pid"];
									$adddata["shi"]=$tmpclient["cid"];
								}else{
									 
									return json_encode(["msg"=>"经销商不存在或已删除","state"=>2]);
								}
							}else if($client=="系统"){
								$tmpclient=Db::table("cg_system")->alias("s")
								->join("cg_jxs j","s.jxs=j.id")
								->join("cg_market_zoning z","j.zoning=z.id")
								->join("cg_market_provice p","j.provice=p.id")
								->join("cg_market_city c","j.city=c.id")
								->where(["s.title"=>$content,"s.isdel"=>0,"j.isdel"=>0])
								->field("j.id as jxs,s.id as xitong,z.id as zid,p.id as pid,c.id as cid")->find();
								if($tmpclient){
									$adddata["jingxiao"]=$tmpclient["jxs"];
									$adddata["xitong"]=$tmpclient["xitong"];
									$adddata["fenqu"]=$tmpclient["zid"];
									$adddata["sheng"]=$tmpclient["pid"];
									$adddata["shi"]=$tmpclient["cid"];
								}else{
									 
									return json_encode(["msg"=>"系统不存或所属经销商已删除","state"=>2]);
								}
							}else if($client=="网点"){
								$tmpclient=Db::table("cg_system")->alias("s")
								->join("cg_jxs j","s.jxs=j.id")
								->join("cg_net n","n.system_id=s.id")
								->join("cg_market_zoning z","j.zoning=z.id")
								->join("cg_market_provice p","j.provice=p.id")
								->join("cg_market_city c","j.city=c.id")
								->where(["n.title"=>$content,"n.isdel"=>0,"j.isdel"=>0,"s.isdel"=>0])
								->field("j.id as jxs,s.id as xitong,n.id as net,z.id as zid,p.id as pid,c.id as cid")->find();
								if($tmpclient){
									$adddata["jingxiao"]=$tmpclient["jxs"];
									$adddata["xitong"]=$tmpclient["xitong"];
									$adddata["wangdian"]=$tmpclient["net"];
									$adddata["fenqu"]=$tmpclient["zid"];
									$adddata["sheng"]=$tmpclient["pid"];
									$adddata["shi"]=$tmpclient["cid"];
								}else{
									 
									return json_encode(["msg"=>"网点不存或所属经销商，系统已删除","state"=>2]);
								}
							}else{
								//错误终止
								
								return json_encode(["msg"=>"系统繁忙请稍后再试","state"=>2]);
							}
						}else{
							
							return json_encode(["msg"=>$client."必须填写","state"=>2]);
						}
					break;
					case 'product_id':
					#产品纬度
						if($content){
							if($product=="品牌"){
								$tmppro=Db::table("pro_brands")->where(["is_actived"=>1,"is_deleted"=>0,"brand_name"=>$content])
								->field("brand_name,id")->find();
								if($tmppro){
									$adddata["pinpai"]=$tmppro["id"];
								}else{
									
									return json_encode(["msg"=>"品牌不存或已删除","state"=>2]);
								}

							}else if($product=="品类"){
								$tmppro=Db::table("pro_category")->alias("c")
								->join("pro_brands b","c.brand_id=b.id")->where(["c.is_actived"=>1,"c.is_deleted"=>0,"c.category_name"=>$content,"b.is_actived"=>1,"b.is_deleted"=>0])
								->field("b.id as brand_name,c.id as category_name")->find();
								if($tmppro){
									$adddata["pinpai"]=$tmppro["brand_name"];
									$adddata["pinlei"]=$tmppro["category_name"];
								}else{
									
									return json_encode(["msg"=>"品类不存或所属品牌已删除","state"=>2]);
								}
							}else if($product=="系列"){
								$tmppro=Db::table("pro_series")->alias("s")
								->join("pro_category c","s.category_id=c.id")
								->join("pro_brands b","c.brand_id=b.id")->where(["c.is_actived"=>1,"c.is_deleted"=>0,"s.series_name"=>$content,"b.is_actived"=>1,"b.is_deleted"=>0,"s.is_actived"=>1,"s.is_deleted"=>0])
								->field("b.id as brand_name,c.id as category_name,s.id as series_name")->find();
								if($tmppro){
									$adddata["pinpai"]=$tmppro["brand_name"];
									$adddata["pinlei"]=$tmppro["category_name"];
									$adddata["xilie"]=$tmppro["series_name"];
								}else{
									
									return json_encode(["msg"=>"系列不存或所属品牌或品类已删除","state"=>2]);
								}
							}else{
								//错误终止
								// Db::rollBack();
								return json_encode(["msg"=>"系统繁忙请稍后再试","state"=>2]);
							}
						}else{
							 
							return json_encode(["msg"=>$product."必须填写","state"=>2]);
						}
					break;
					case 'area_id':
					#区域纬度
					if($content){

						if($area=="大区"){
							$tmparea=Db::table("cg_market_zoning")->where(["isdel"=>0,"title"=>$content])->field("id as title")->find();
							if($tmparea){
								$adddata["fenqu"]=$tmparea["title"];
							}else{
								
								return json_encode(["msg"=>"大区不存在或已删除","state"=>2]);
							}
						}else if($area=="省份"){
							$tmparea=Db::table("cg_market_zoning")->alias("z")
							->join("cg_market_provice p","p.zoning=z.id")
							->where(["p.isdel"=>0,"z.isdel"=>0,"p.title"=>$content])->field("z.id as ztitle,p.id as ptitle")->find();
							if($tmparea){
								$adddata["fenqu"]=$tmparea["ztitle"];
								$adddata["sheng"]=$tmparea["ptitle"];
							}else{
								 
								return json_encode(["msg"=>"省份不存在或上级大区已删除","state"=>2]);
							}
						}else if($area=="城市"){
							$tmparea=Db::table("cg_market_zoning")->alias("z")
							->join("cg_market_provice p","p.zoning=z.id")
							->join("cg_market_city c","c.provice=p.id")
							->where(["p.isdel"=>0,"z.isdel"=>0,"c.isdel"=>0,"c.title"=>$content])->field("z.id as ztitle,p.id as ptitle,c.id as ctitle")->find();
							if($tmparea){
								$adddata["fenqu"]=$tmparea["ztitle"];
								$adddata["sheng"]=$tmparea["ptitle"];
								$adddata["shi"]=$tmparea["ctitle"];
							}else{
								 
								return json_encode(["msg"=>"城市不存在或上级省份,大区已删除","state"=>2]);
							}
						}else{
							
							return json_encode(["msg"=>"系统繁忙请稍后再试","state"=>2]);
						}
					}else{
						 
						return json_encode(["msg"=>$area."必须填写","state"=>2]);
					}

					break;
					case 'feiyong_id':
					#费用纬度
					if($content){
						if($feiyong=="一级费用科目"){
							$tmpfeiyong=Db::table("cg_feiyongkemu1")->where(["title"=>$content])->field("id as title1")->find();
							if($tmpfeiyong){
								$adddata["yijifeiyong"]=$tmpfeiyong["title1"];
							}else{
								
								return json_encode(["msg"=>"一级费用科目不存在或已删除","state"=>2]);
							}
						}else if($feiyong=="二级费用科目"){
							$tmpfeiyong=Db::table("cg_feiyongkemu1")->alias("f1")
							->join("cg_feiyongkemu2 f2","f2.pid=f1.id")
							->where(["f2.title"=>$content])->field("f1.id as title1,f2.id as title2")->find();
							if($tmpfeiyong){
								$adddata["yijifeiyong"]=$tmpfeiyong["title1"];
								$adddata["erjifeiyong"]=$tmpfeiyong["title2"];
							}else{
								
								return json_encode(["msg"=>"二级费用科目不存在或已删除","state"=>2]);
							}
						}else if($feiyong=="三级费用科目"){
							$tmpfeiyong=Db::table("cg_feiyongkemu1")->alias("f1")
							->join("cg_feiyongkemu2 f2","f2.pid=f1.id")
							->join("cg_feiyongkemu3 f3","f3.pid=f2.id")
							->where(["f3.title"=>$content])->field("f1.id as title1,f2.id as title2,f3.id as title3")->find();
							if($tmpfeiyong){
								$adddata["yijifeiyong"]=$tmpfeiyong["title1"];
								$adddata["erjifeiyong"]=$tmpfeiyong["title2"];
								$adddata["sanjifeiyong"]=$tmpfeiyong["title3"];
							}else{
								
								return json_encode(["msg"=>"三级费用科目不存在或已删除","state"=>2]);
							}
						}else{
							 
							return json_encode(["msg"=>"系统繁忙请稍后再试","state"=>2]);
						}
					}else{
						
						return json_encode(["msg"=>$feiyong."必须填写","state"=>2]);
					}
					break;
					case "cainian":
					//财年
					if($content){
						$tmpcainian=Db::table("cg_cainian")->where(["title"=>$content])->find();
						if($tmpcainian){
							$adddata["cainian"]=$tmpcainian["id"];
						}else{
							 
							return json_encode(["msg"=>"财年不存在","state"=>2]);
						}
						
					}else{
						
						return json_encode(["msg"=>"财年必须填写","state"=>2]);
					}
					break;
					case "kaoheqi":
					#考核期
					if($content){
						if($kaoheqi=="一级考核期"){
							$tmpkaohe=Db::table("cg_kaoheqi1")->alias("k1")
							->join("cg_cainian c","k1.cid=c.id")->where(["k1.title"=>$content,"c.id"=>$adddata["cainian"]])->field("k1.id as title1")->find();
							if($tmpkaohe){
								$adddata["yijikaoheqi"]=$tmpkaohe["title1"];
							}else{
								 
								return json_encode(["msg"=>$kaoheqi."不存在或财年不符","state"=>2]);
							}
						}else if($kaoheqi=="二级考核期"){
							//return json_encode(["msg"=>$content,"state"=>2]);
							$tmpkaohe=Db::table("cg_kaoheqi1")->alias("k1")->join("cg_kaoheqi2 k2","k2.pid=k1.id")
							->join("cg_cainian c","k1.cid=c.id")->where(["k2.title"=>$content,"c.id"=>$adddata["cainian"]])->field("k1.id as title1,k2.id as title2")->find();
							if($tmpkaohe){
								$adddata["yijikaoheqi"]=$tmpkaohe["title1"];
								$adddata["erjikaoheqi"]=$tmpkaohe["title2"];
							}else{
								
								return json_encode(["msg"=>$kaoheqi."不存在或财年不符","state"=>2]);
							}
						}else{
							
							return json_encode(["msg"=>"系统繁忙请稍后再试","state"=>2]);
						}
					}else{
						
						return json_encode(["msg"=>$kaoheqi."必须填写","state"=>2]);
					}
					break;
					case 'people_id':
					if($content){
						$eid=db("employee")->where("em_name='$content'")->value("id");
						if($eid)
							$adddata["people"]=$eid;
						else
							return json_encode(["msg"=>"人员不存在","state"=>2]);
					}else{
						
						return json_encode(["msg"=>"人员必须填写","state"=>2]);
					}
					break;
					case 'sale_id':
					if($content){
						$adddata["sale"]=$content;
					}else{
						 
						return json_encode(["msg"=>"毛销售额必须填写","state"=>2]);
					}
					break;
					case 'sale_real_id':
					if($content){
						$adddata["sale_real"]=$content;
					}else{
						
						return json_encode(["msg"=>"净销售额必须填写","state"=>2]);
					}
					break;
					case 'profit_id':
					if($content){
						$adddata["profit"]=$content;
					}else{
						 
						return json_encode(["msg"=>"利润目标必须填写","state"=>2]);
					}
					break;
					case 'depart_id':
					if($content){
						$tmpdep=Db::table("department")->where(["de_name"=>$content,"is_actived"=>1,"is_deleted"=>0])->find();
						if($tmpdep){
							$adddata["depart"]=$tmpdep["id"];
						}else{
							 
							return json_encode(["msg"=>"部门不存在或已删除","state"=>2]);
						}
					}else{
						
						return json_encode(["msg"=>"部门必须填写","state"=>2]);
					}
					break;
					case 'yusuan':
					if($content){
						$adddata["feiyong"]=$content;
					}else{
						
						return json_encode(["msg"=>"预算必须填写","state"=>2]);
					}
					break;
					default:
						# code...
						break;
				}
				
				//$dataset[] = $sheet->getCell($c.$row)->getValue();
				//echo $c.$row.":".$sheet->getCell($c.$row)->getValue()."<br />";
			}
			$alldata[]=$adddata;
		}
		//print_r($alldata);exit;
		  Db::startTrans();
       try{
			$res2=Db::table("target_apply")->insertGetId($datata);
			for($i=0;$i<count($alldata);$i++){
				$alldata[$i]["apply_id"]=$res2;
			}
			$res1=Db::table("target_apply_detail")->insertAll($alldata);
		}catch(\Exception $e){

                Db::rollBack();
                return json_encode(["msg"=>"系统繁忙请稍后再试","state"=>2]);
            }
            if($res1&&$res2){
            	if($state==3){
            		$backjson=$this->ystijiao($res2);
            		
            		Db::commit();
            		return $backjson;

            	}else{
            		Db::commit(); //事物提交
               		return json_encode(["msg"=>"操作已成功","state"=>1]);
            	}
                
            }else{
                Db::rollBack();
                return json_encode(["msg"=>"系统繁忙请稍后再试","state"=>2]);
            }
		//print_r($alldata);
	}

	//根据财年筛选一级考核期
	public function cainian_kaohe1(){
		  $request = Request::instance();

        
        $data=$request->post();
        $cainian=$data["cainian"];
        if($cainian){
        	$arr=Db::table("cg_kaoheqi1")->alias("k")->join("cg_cainian c","k.cid=c.id")->where(["c.title"=>$cainian])
        	->field("k.title")->select();
        	return json_encode($arr);
        }else{
        	return 0;
        }
	}

	//考核期12联动
	public function kaohe1_kaohe2(){
 		$request = Request::instance();
        $data=$request->post();
        $kaohe1=$data["kaohe1"];
        if($kaohe1){
        	$arr=Db::table("cg_kaoheqi1")->alias("k1")->join("cg_kaoheqi2 k2","k2.pid=k1.id")->where(["k1.title"=>$kaohe1])->field("k2.title")->select();
        	return json_encode($arr);
        }else{
        	return 0;
        }

	}

	//费用12联动
	public function feiyong1_feiyong2(){
		$request = Request::instance();
        $data=$request->post();
        $feiyong1=$data["feiyong1"];
        
	}

}